<?php
require_once('utils.php');


class DBManager {
	static $db = null;

	/********************************
	 *		QUERY STATEMENT
	 * *****************************/
	static $qUserById = null;
	static $qSonnaUserById = null;
	static $qUsersId = null;
	static $qTokens = null;
	static $qObj = null;
	static $qFriend = null;

	/********************************
	 *		INSERT STATEMENT
	 * *****************************/
	static $iFBUserPP = null;
	static $iSonnaUser = null;
	static $iFriend = null;
	static $iSearch = null;
	static $iFeed = null;
	static $iError = null;
	static $iObj = null;
	static $iComments = null;
	static $iE2M = null;
	static $iStatus = null;
	static $iLike = null;
	static $iMsgTags = null;
	static $iStoryTags = null;
	static $iTo = null;
	static $iFrom = null;
	static $iVia = null;
	static $iHome = null;
	static $iOutboxes = null;

	/********************************
	 *	INSERT/UPDATE STATEMENT
	 * *****************************/
	static $iuToken = null;
	static $iuFBUser = null;

	/********************************
	 *	UPDATE STATEMENT
	 * *****************************/
	static $uLastCron = null;


	/**
	 *Returns a new connection to the database
	 */
	private static function getConnection() {
		$dbhost="159.213.137.77";
		$dbuser="fxb";
		$dbpass="F_123_x_456_B";
		$dbname="fxb";
		$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
		$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		return $dbh;
	}

	/**
	 *
	 * SOLO PER DEBUG!!!!!
	 * rimettere anche le credenziali vecchie per accedere al db.
	 *
	 */
	public static function clear(){
		if(is_null(self::$db))
			self::$db = self::getConnection();
self::$db->exec('SET FOREIGN_KEY_CHECKS=0;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`friends`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`statuses`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`feeds`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`logs`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`comments`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`outboxes`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`message_tags`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`story_tags`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`to`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`from`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`via`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`objects`;');
		self::$db->exec('TRUNCATE TABLE `fxb`.`homes`;');
self::$db->exec('SET FOREIGN_KEY_CHECKS=1;');
	}

	public static function beginTransaction(){
		if(is_null(self::$db))
			self::$db = self::getConnection();
		self::$db->beginTransaction();
	}

	public static function commitTransaction(){
		self::$db->commit();
	}

	public static function rollBack(){
		self::$db->rollBack();
	}

	/**
	 * Prepares a new statemet to insert a new message tag in the database.
	 * If the tagged user has not been yet saved in the database, his/her public
	* profile must be saved first.
	 * @return a new statement to insert the message tag.
	 */
	public static function statementInsertOutboxes(){
		if(is_null(self::$iOutboxes)){
			$sql = 
				'INSERT INTO `outboxes`'
				.' (`id`, `idUser`, `message`, `updated_time`)'
				.' VALUES'
				.' (:id, :idUser, :message, :updated_time)';
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iOutboxes = self::$db->prepare($sql);
		}
		return self::$iOutboxes;
	}


	/**
	 * Prepares a new statemet to insert a new message tag in the database.
	 * If the tagged user has not been yet saved in the database, his/her public
	* profile must be saved first.
	 * @return a new statement to insert the message tag.
	 */
	public static function statementInsertHome(){
		if(is_null(self::$iHome)){

			$sql = 'INSERT INTO `homes`'
				.' (`id`, `idUser`, `caption`, `created_time`, `description`, `is_published`, `link`, `message`, `name`, `object_id`, `type`, `parent_id`, `source`, `story`)'
				.' VALUES'
				.' (:id, :idUser, :caption, :created_time, :description, :is_published, :link, :message, :name, :object_id, :type, :parent_id, :source, :story)';

 
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iHome= self::$db->prepare($sql);
		}
		return self::$iHome;
	}

	/**
	 * Prepares a new statemet to insert a new message tag in the database.
	 * If the tagged user has not been yet saved in the database, his/her public
	* profile must be saved first.
	 * @return a new statement to insert the message tag.
	 */
	public static function statementInsertVia(){
		if(is_null(self::$iVia)){
			$sql = 
				'INSERT INTO `via`'
				.' (`refer`, `via`, `category`, `name`)'
				.' VALUES'
				.' (:refer, :via, :category, :name)';
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iVia = self::$db->prepare($sql);
		}
		return self::$iVia;
	}

	/**
	 * Prepares a new statemet to insert a new message tag in the database.
	 * If the tagged user has not been yet saved in the database, his/her public
	* profile must be saved first.
	 * @return a new statement to insert the message tag.
	 */
	public static function statementInsertFrom(){
		if(is_null(self::$iFrom)){
			$sql = 
				'INSERT INTO `from`'
				.' (`refer`, `from`, `category`, `name`)'
				.' VALUES'
				.' (:refer, :from, :category, :name)';
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iFrom= self::$db->prepare($sql);
		}
		return self::$iFrom;
	}

	/**
	 * Prepares a new statemet to insert a new message tag in the database.
	 * If the tagged user has not been yet saved in the database, his/her public
	* profile must be saved first.
	 * @return a new statement to insert the message tag.
	 */

	public static function statementInsertTo(){
		if(is_null(self::$iTo)){
			$sql = 
				'INSERT INTO `to`'
				.' (`refer`, `to`, `category`, `name`)'
				.' VALUES'
				.' (:refer, :to, :category, :name)';
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iTo= self::$db->prepare($sql);
		}
		return self::$iTo;
	}

	/**
	 * Prepares a new statemet to insert a new message tag in the database.
	 * If the tagged user has not been yet saved in the database, his/her public
	* profile must be saved first.
	 * @return a new statement to insert the message tag.
	 */
	public static function statementInsertStoryTags(){
		if(is_null(self::$iStoryTags)){
			$sql = 
				'INSERT INTO story_tags'
				.' (`refer`, `from`, `tagged`, `offset`, `length`, `type`)'
				.' VALUES'
				.' (:refer, :from, :tagged, :offset, :length, :type)';
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iStoryTags= self::$db->prepare($sql);
		}
		return self::$iStoryTags;
	}

	/**
	 * Prepares a new statemet to insert a new message tag in the database.
	 * If the tagged user has not been yet saved in the database, his/her public
	* profile must be saved first.
	 * @return a new statement to insert the message tag.
	 */
	public static function statementInsertMessageTags(){
		if(is_null(self::$iMsgTags)){
			$sql = 
				'INSERT INTO message_tags'
				.' (`refer`, `from`, `tagged`, `offset`, `length`, `type`)'
				.' VALUES'
				.' (:refer, :from, :tagged, :offset, :length, :type)';
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iMsgTags= self::$db->prepare($sql);
		}
		return self::$iMsgTags;
	}

	/**
	 * Returns all the rows of the tokens table.
	 * @return an array containing the tokens table row
	 */
	public static function statementQueryFriend(){
		if(is_null(self::$qFriend)){
			$sql_query = 'SELECT * 
				FROM `friends`
				WHERE `idUser`=:idUser AND `idFriend`=:idFriend';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$qFriend = self::$db->prepare($sql_query);
		}
		return self::$qFriend;
	}

	/**
	 * Returns a statement to update the last cron update timestamp in the database.
	 * @return a PDO statement update the last cron update timestamp in the database.
	 */
	public static function statementUpdateLastCron(){
		if(is_null(self::$uLastCron)){
			$sql_query = 'UPDATE `tokens` 
					SET 
						`lastCronUpdate`= :update
					WHERE 
						`fid` = :id';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$uLastCron = self::$db->prepare($sql_query);
		}
		return self::$uLastCron;
	}

	/**
	 * Returns a statement to insert a new longterm access token in the database.
	 * The toke is inserted using the facebook user id as index. If already exists
	 * a record for that user, it updates the toke.
	 * @return a PDO statement used to insert an access token.
	 */
	public static function statementInsertOrUpdateToken(){
		if(is_null(self::$iuToken)){
			$sql_query = 'INSERT INTO `tokens`
				(`fid`, `token`, `lastCronUpdate`) 
				VALUES 
				(:id, :token, 0)
				ON DUPLICATE KEY UPDATE `token` = :update ;';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$iuToken = self::$db->prepare($sql_query);
		}
		return self::$iuToken;
	}

	/**
	 * Returns all the rows of the tokens table.
	 * @return an array containing the tokens table row
	 */
	public static function statementQueryTokens(){
		if(is_null(self::$qTokens)){
			$sql_query = 'SELECT `fid` AS `id`, `token`, UNIX_TIMESTAMP(`lastCronUpdate`) AS `lastCronUpdate`
				FROM `tokens`';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$qTokens = self::$db->prepare($sql_query);
		}
		return self::$qTokens;
	}

	/**
	 * Returns all the rows of the tokens table.
	 * @return an array containing the tokens table row
	 */
	public static function statementQueryObject(){
		if(is_null(self::$qObj)){
			$sql_query = 
				'SELECT `id`'
				.' FROM `objects`'
				.' WHERE `id` = :id';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$qObj = self::$db->prepare($sql_query);
		}
		return self::$qObj;
	}

	/**
	 * Returns a statement to retrieve all the user ids present on the db
	 * @return a PDO statement used to query the user ids.
	 */
	public static function statementQueryUsersId(){
		if(is_null(self::$qUsersId)){
			$sql_query = 'SELECT id FROM users';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$qUsersId = self::$db->prepare($sql_query);
		}
		return self::$qUsersId;
	}

	public static function statementQuerySonnaUserById(){
		if(is_null(self::$qSonnaUserById)){
			$sql_query = 'SELECT * FROM sonnaUsers WHERE facebookId = :fId';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$qSonnaUserById = self::$db->prepare($sql_query);
		}
		return self::$qSonnaUserById;
	}

	public static function statementQueryUserById(){
		if(is_null(self::$qUserById)){
			$sql_query = 'SELECT * FROM users WHERE id = :place';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$qUserById = self::$db->prepare($sql_query);
		}
		return self::$qUserById;
	}

	/**
	 * Prepares a new statemet to insert a new user in the database.
	 * Just the public profile of the user will be saved.
	 * @return a new statement to insert the user in the db.
	 */
	public static function statementInsertFBUserPublicProfile(){
		if(is_null(self::$iFBUserPP)){
			$sql = 
				'INSERT INTO users'
				.' (id, name, first_name, last_name, username, gender, locale)'
				.' VALUES'
				.' (:id, :name, :first_name, :last_name, :username, :gender, :locale)';
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iFBUserPP = self::$db->prepare($sql);
		}
		return self::$iFBUserPP;
	}

	/**
	 * Prepares a new statemet to insert a new user in the database.
	 * @return a new statement to insert the user in the db.
	 */
	public static function statementInsertOrUpdateFBUser(){	//probabilmente va fatta diventare insert or update
		if(is_null(self::$iuFBUser)){
			$sql = 
				'INSERT INTO users'
				.' (id, name, first_name, last_name, link, username, birthday, hometown, location, bio, quotes, work, sports, favorite_teams, favorite_athletes, education, gender, relationship_status, religion, political, email, website, timezone, locale, verified, updated_time)'
				.' VALUES'
				.' (:id, :name, :first_name, :last_name, :link, :username, :birthday, :hometown, :location, :bio, :quotes, :work, :sports, :favorite_teams, :favorite_athletes, :education, :gender, :relationship_status, :religion, :political, :email, :website, :timezone, :locale, :verified, :updated_time)'
				.' ON DUPLICATE KEY UPDATE'
				.' link = :ulink, birthday = :ubirthday, hometown = :uhometown, location = :ulocation, bio = :ubio, quotes = :uquotes, work = :uwork, sports = :usports, favorite_teams = :ufavorite_teams, favorite_athletes = :ufavorite_athletes, education = :ueducation, relationship_status = :urelationship_status, religion = :ureligion, political = :upolitical, email = :uemail, website = :uwebsite, timezone = :utimezone, verified = :uverified, updated_time = :uupdated_time';
				
			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iuFBUser = self::$db->prepare($sql);
		}
		return self::$iuFBUser;
	}

	/**
	 * Prepares a new statemet to insert a new feed in the database.
	 * @return a new statement to insert the feed in the db.
	 */
	public static function statementInsertFeed(){
		if(is_null(self::$iFeed)){
			$sql ='INSERT INTO `fxb`.`feeds`'
				.' (`id`, `idUser`, `caption`, `coordinates`, `created_time`, `description`, `feed_targeting`, `is_published`, `link`, `message`, `name`, `object_id`, `parent_id`, `place`, `source`, `type`, `status_type`, `story`, `timeline_visibility`)'
				.' VALUES '
				.'(:id, :idUser, :caption, :coordinates, :created_time, :description, :feed_targeting, :is_published, :link, :message, :name, :object_id, :parent_id, :place, :source, :type, :status_type, :story, :timeline_visibility);'; 

			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iFeed = self::$db->prepare($sql);
		}
		return self::$iFeed;
	}

	/**
	 * Prepares a new statemet to insert a new status
	 * @return a new statement to insert a new status.
	 */
	public static function statementInsertStatus(){
		if(is_null(self::$iStatus)){
			$sql = 'INSERT INTO `statuses` '
				.'(`id`, `from`, `message`)'
				.' VALUES'
				.'(:id, :from, :message)';

			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iStatus = self::$db->prepare($sql);
		}
		return self::$iStatus;
	}

	/**
	 * Prepares a new statemet to insert a new like
	 * @return a new statement to insert a new like.
	 */
	public static function statementInsertLike(){
		if(is_null(self::$iLike)){
			$sql = 'INSERT IGNORE INTO `likes` '
				.'(`idUser`, `what`)'
				.' VALUES'
				.'(:idUser, :what)';

			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iLike = self::$db->prepare($sql);
		}
		return self::$iLike;
	}

	/**
	 * Prepares a new statemet to insert a new message
	 * @return a new statement to insert a new message.
	 */
	public static function statementInsertComments(){
		if(is_null(self::$iComments)){
			$sql = 'INSERT INTO `comments` '
				.'(`id`, `refer`, `message`, `user_likes`, `created_time`)'
				.' VALUES'
				.'(:id, :refer, :message, :user_likes, :created_time)';

			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iComments = self::$db->prepare($sql);
		}
		return self::$iComments;
	}

	/**
	 * Prepares a new statemet to insert a new row in the elem2messages table.
	 * @return a new statement to insert a new  row in the elem2messages table.
	 */
	public static function statementInsertElem2Msg(){
		if(is_null(self::$iE2M)){
			$sql = 'INSERT INTO `elem2messages` '
				.'(`idObj`, `idMsg`)'
				.' VALUES'
				.' (:idObj, :idMsg);';

			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iE2M = self::$db->prepare($sql);
		}
		return self::$iE2M;
	}

	/**
	 * Prepares a new statemet to insert a new FB object.
	 * @return a new statement to insert a new FB object.
	 */
	public static function statementInsertObj(){
		if(is_null(self::$iObj)){
			$sql = 'INSERT INTO `objects` '
				.'(`id`, `type`)'
				.' VALUES'
				.' (:id, :type);';

			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iObj = self::$db->prepare($sql);
		}
		return self::$iObj;
	}

	/**
	 * Prepares a new statemet to insert a new sonna user in the database.
	 * @return a new statement to insert the user in the db.
	 */
	public static function statementInsertSonnaUser(){
		if(is_null(self::$iSonnaUser)){
			$sql = 'INSERT INTO `sonnaUsers` '
				.'(`id`, `facebookId`, `from`)'
				.' VALUES'
				.' (:drupal_user, :fid, :from);';

			if(is_null(self::$db))
				self::$db = self::getConnection();

			self::$iSonnaUser = self::$db->prepare($sql);
		}
		return self::$iSonnaUser;
	}

	/**
	 * Prepares a new statemet to insert a new friend in the database.
	 * @return a new statement to insert the friend in the db.
	 */
	public static function statementInsertFriend(){
		if(is_null(self::$iFriend)){
			$sql = 'INSERT INTO friends (idUser, idFriend) VALUES (:uid, :fid);';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$iFriend = self::$db->prepare($sql);
		}
		return self::$iFriend;
	}

	/**
	 * Prepares a new statemet to insert a new research in the database.
	 * @return a new statement to insert the search in the db.
	 */
	public static function statementInsertSearch(){
		if(is_null(self::$iSearch)){
			$sql = 'INSERT INTO searches (uid, search, engine) VALUES (:uid, :search, :engine);';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$iSearch = self::$db->prepare($sql);
		}
		return self::$iSearch;
	}

	/**
	 * Prepares a new statemet to insert a new error in the database.
	 * @return a new statement to insert the error in the db.
	 */
	public static function statementInsertError(){
		if(is_null(self::$iError)){
			$sql = 'INSERT INTO `logs` '
				.'(`logLevel`, `content`, `uid`) '
				.'VALUES '
				.'(:lvl, :content, :uid)';
			if(is_null(self::$db))
				self::$db = self::getConnection();
			self::$iError = self::$db->prepare($sql);
		}
		return self::$iError;
	}


	/********************************
	 *	INSERT functions
	 ********************************/
	/**
	 * Inserts a new object. If something wrong happens
	 * it raise an exception.
	 * @param id The object facebook id
	 * @param type the type of object
	 */
	public static function insertObj($id, $type){
		$stmt = self::statementInsertObj();
		$stmt->bindValue(':id', $id, PDO::PARAM_STR);
		$stmt->bindValue(':type', $type, PDO::PARAM_STR);
		$stmt->execute();
		$stmt->closeCursor();
	}
	
	/**
	 * Checks if the database contains an object with the give id
	 * @param id The object facebook id
	 * @return true if the object exists in the DB, false otherwise
	 * @throw An exception is thrown if something wrong happened with the DB.
	 */
	public static function existsObj($id){
		$stmt = self::statementQueryObject();
		$stmt->bindValue(':id', $id, PDO::PARAM_STR);
		$stmt->execute();
		$res = count($stmt->fetchAll()) > 0;
		$stmt->closeCursor();
		return $res;
	}

	/**
	 * Verifies if the user denoted by uid is already present in the database.
	 * @param db reference to an open database connection.
	 * @param uid User identifier.
	 * @throw PODExceptiion if something wrong happens during the interaction with the db
	 * @return true if the user has been already inserted in the database, false otherwise.
	 */
	function existUser($id){
		global $log;
		$stmt = self::statementQueryUserById();
		$stmt->bindValue(':place', $id, PDO::PARAM_STR);
		$stmt->execute();
		$result = count($stmt->fetchAll()) > 0;
		$stmt->closeCursor();

		return $result;
	}

	/**
	 * Verifies if the user denoted by the facebook identifier provided is already present in the database.
	 * @param fId User identifier.
	 * @throw PODExceptiion if something wrong happens during the interaction with the db
	 * @return true if the user has been already inserted in the database, false otherwise.
	 */
	function existSonnaUser($fId){
		global $log;
		$stmt = self::statementQuerySonnaUserById();
		$stmt->bindValue(':fId', $fId, PDO::PARAM_STR);
		$stmt->execute();
		$result = count($stmt->fetchAll()) > 0;
		$stmt->closeCursor();

		return $result;
	}

	/**
	 * Verifies if the friend relationship between the two users has been already inserted
	* in the database.
	 * @param idUser User facebook identifier.
	* @param idFriend Friend facebook id
	 * @throw PODExceptiion if something wrong happens during the interaction with the db
	 * @return true if the friend relation between the two users has been already inserted in the database, false otherwise.
	 */
	function existFriendRelation($idUser, $idFriend){
		global $log;
		$stmt = self::statementQueryFriend();
		$stmt->bindValue(':idUser', $idUser, PDO::PARAM_STR);
		$stmt->bindValue(':idFriend', $idFriend, PDO::PARAM_STR);
		$stmt->execute();
		$result = count($stmt->fetchAll()) > 0;
		$stmt->closeCursor();

		return $result;
	}
	/**
	 * Adds the Facebook public profile of an user in the database.
	 * @param id The user facebook id
	 * @return true if the object exists in the DB, false otherwise
	 * @throw An exception is thrown if something wrong happened with the DB.
	 */
	public static function addUserPublicProfile($id, $name, $first_name, $last_name, $username, $gender, $locale){
		global $log;
		$stmt = self::statementInsertFBUserPublicProfile();
		$stmt->bindValue(':id', $id, PDO::PARAM_STR);
		$stmt->bindValue(':name', $name, PDO::PARAM_STR);
		$stmt->bindValue(':first_name', $first_name, PDO::PARAM_STR);
		$stmt->bindValue(':last_name', $last_name, PDO::PARAM_STR);
		$stmt->bindValue(':username', $username, PDO::PARAM_STR);
		$stmt->bindValue(':gender', $gender, PDO::PARAM_STR);
		$stmt->bindValue(':locale', $locale, PDO::PARAM_STR);
		$stmt->execute();
		$stmt->closeCursor();
	}

	/**
	 * Create a new entry in the elem2messages table to connect the 
	 * object with the message
	 * @param elem the object id
	 * @param msg the message id
	 * @throws PDOException if something wrong happens.
	 */
	public static function joinElem2Message($elem, $msg){
		$stmt = self::statementInsertElem2Msg();
		$stmt->bindValue(':idObj', $elem, PDO::PARAM_STR);
		$stmt->bindValue(':idMsg', $msg, PDO::PARAM_STR);
		$stmt->execute();
		$stmt->closeCursor();
	}
}

?>
